import os
import pandas as pd
from sqlalchemy import create_engine
from urllib.parse import quote_plus


data_path = '../data'

data_files = [file for file in os.listdir(data_path) if file.endswith('.csv')]

engine = create_engine(f'mysql+pymysql://root:{quote_plus("123456")}@127.0.0.1:3306/air-quality')

for data_file in data_files:
    data_file_path = os.path.join(data_path, data_file)
    df = pd.read_csv(data_file_path)
    df.drop(columns=['No'], inplace=True)
    df.rename(columns={'PM2.5': 'pm25'}, inplace=True)
    df.bfill(inplace=True)
    df.ffill(inplace=True)
    df.to_sql(name='t_prsa_data', con=engine, if_exists='append', index=False)
